﻿DELIMITER //

-- UCC/EAN-128 parse
--
DROP FUNCTION IF EXISTS fnEAN128
//

--
--
CREATE FUNCTION fnEAN128 
  (_EAN128 TEXT) RETURNS TEXT
BEGIN
  DECLARE _N INTEGER;

  DECLARE _AI VARCHAR(2);
  DECLARE _AI01 VARCHAR(20);
  DECLARE _AI02 VARCHAR(20);
  DECLARE _AI15 VARCHAR(20);
  DECLARE _AI17 VARCHAR(20);
  DECLARE _AI31 DECIMAL(18,6);
  DECLARE _AI37 DECIMAL(18,6);

  label: LOOP

    SET _AI = SUBSTRING(_EAN128, 1, 2);

    IF (_AI = '') THEN
      LEAVE label;
    END IF;

    -- GTIN 14
    IF (_AI = N'01') THEN
    
      SET _AI01 = SUBSTRING(_EAN128, 3, 14);
      
      SET _EAN128 = SUBSTRING(_EAN128, 17, 128);

    -- GTIN 14
    ELSEIF (_AI = N'02') THEN

      SET _AI01 = SUBSTRING(_EAN128, 3, 14);
      
      SET _EAN128 = SUBSTRING(_EAN128, 17, 128);

    -- Best before
    ELSEIF (_AI = N'15') THEN

      SET _AI15 = SUBSTRING(_EAN128, 3, 6);
      
      SET _EAN128 = SUBSTRING(_EAN128, 9, 128);

    -- Expire date
    ELSEIF (_AI = N'17') THEN

      SET _AI17 = SUBSTRING(_EAN128, 3, 6);
      
      SET _EAN128 = SUBSTRING(_EAN128, 9, 128);


    -- Netto weight
    ELSEIF (_AI = N'31') THEN
          
      SET _AI31 = CONVERT(SUBSTRING(_EAN128, 5, 6), DECIMAL(18,6)) / POWER(10, CONVERT(SUBSTRING(_EAN128, 3, 2), DECIMAL(18,6)));
      
      SET _EAN128 = SUBSTRING(_EAN128, 11, 128);

    -- Quantity
    ELSEIF (_AI = N'37') THEN

      SET _N = LOCATE(CHAR(29), SUBSTRING(_EAN128, 3, 8));

      IF (_N = 0) THEN
        SET _N = 8;
        SET _AI37 = CONVERT(SUBSTRING(_EAN128, 3, 8), DECIMAL(18,6));
      ELSE
        SET _AI37 = CONVERT(SUBSTRING(_EAN128, 3, _N - 1), DECIMAL(18,6));
      END IF;

      SET _EAN128 = SUBSTRING(_EAN128, _N + 3, 128);

    ELSE
      LEAVE label;
    END IF;

  END LOOP label;
 
  RETURN(
    CONCAT('<Ean128>', 
             '<AI01>', IFNULL(_AI01, ''), '</AI01>', 
             '<AI02>', IFNULL(_AI02, ''), '</AI02>', 
             '<AI15>', IFNULL(_AI15, ''), '</AI15>', 
             '<AI17>', IFNULL(_AI17, ''), '</AI17>', 
             '<AI31>', IFNULL(CONVERT(_AI31 USING utf8), ''), '</AI31>', 
             '<AI37>', IFNULL(CONVERT(_AI37 USING utf8), ''), '</AI37>', 
           '</Ean128>')
        );
END
//
